﻿/*---------------------------------------------------------------------------------
-----------------***DaLianQianYi***-----------------------
-----------------作者：大连仟亿科技------------------------------------------------------
-----------------联系作者：www.qianyikeji.com----------------------------------
-----------------时间：2024/7/15 14:05:13------------------------------------------
----------------------------------------------------------------------------------*/


using Donal.Xms.Common;
using Donal.Xms.DBUtility;
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Donal.Xms.DAL
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public partial class spec_prices
    {
        private string databaseprefix; //数据库表名前缀
        public spec_prices(string _databaseprefix)
        {
            databaseprefix = _databaseprefix;
        }

        #region 基本方法================================
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from  " + databaseprefix + "spec_prices");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.spec_prices model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();//数据字段
            StringBuilder str2 = new StringBuilder();//数据参数
                                                     //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("insert into " + databaseprefix + "spec_prices(");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + ",");//拼接字段
                        str2.Append("@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") values (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY;");
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), paras.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.spec_prices model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("update  " + databaseprefix + "spec_prices set ");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(" where id=@id ");
            paras.Add(new SqlParameter("@id", model.id));
            return DbHelperSQL.ExecuteSql(strSql.ToString(), paras.ToArray()) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from  " + databaseprefix + "spec_prices ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.spec_prices GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.spec_prices model = new Model.spec_prices();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                str1.Append(p.Name + ",");//拼接字段
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + databaseprefix + "spec_prices");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM " + databaseprefix + "spec_prices");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + databaseprefix + "spec_prices");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        #endregion

        #region 扩展方法================================

        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetListView(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM view_spec_prices");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }

        /// <summary>
        /// 返回指定列
        /// </summary>
        public string GetValue(int id, string fileName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 " + fileName + " from " + databaseprefix + "spec_prices");
            strSql.Append(" where id=" + id);
            string title = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(title))
            {
                return "";
            }
            return title;
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "spec_prices set " + strValue);
            strSql.Append(" where id=" + id);
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 将对象转换实体
        /// </summary>
        public Model.spec_prices DataRowToModel(DataRow row)
        {
            Model.spec_prices model = new Model.spec_prices();
            if (row != null)
            {
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    //查找实体是否存在列表相同的公共属性
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        proInfo.SetValue(model, row[i], null);//用索引值设置属性值
                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 获取差异价格
        /// 1、规格--固定价格--会员
        /// 2、规格--固定价格--组别
        /// 3、规格--百分比--会员
        /// 4、规格--百分比--组别
        /// 5、商品--百分比--会员（商品无法设置固定价）
        /// 6、商品--百分比--组别（商品无法设置固定价）
        /// 7、品类--百分比--会员（品类无法设置固定价）
        /// 8、品类--百分比--组别（品类无法设置固定价）
        /// 9、会员组别--百分比
        /// 10、游客或登录--百分比（显示价格时）
        /// </summary>
        /// <param name="company_name">会员用户名</param>
        /// <param name="product_title">商品名称</param>
        /// <param name="group_name">会员组别名称</param>
        /// <param name="groupDiscount">会员组别中定义的折扣百分比</param>
        /// <param name="goods_title">规格名称</param>
        /// <param name="discount_remark">返回折扣规格</param>
        /// <param name="category_title">品类名称</param>
        /// <param name="categoryId">品类ID</param>
        /// <param name="productId">商品ID</param>
        /// <param name="goodsId">规格ID</param>
        /// <param name="is_member">是否为会员</param>
        /// <param name="orgPrice">商品原价</param>
        /// <param name="userGroupId">会员或个人组别ID</param>
        /// <param name="companyId">会员ID</param>
        public decimal GetSpecPrice(bool is_member, int goodsId, string goods_title, int productId, string product_title, int categoryId, string category_title, int companyId, string company_name, int userGroupId, string group_name, decimal groupDiscount, decimal orgPrice, out string discount_remark)
        {
            groupDiscount = groupDiscount / 100;

            decimal discount = 0;
            string sql = "select price from dt_spec_prices where ";

            if (companyId == 0)
            {
                discount_remark = "10、游客或登录--百分比：游客或登录--" + groupDiscount + "%";
                return decimal.Round(groupDiscount * orgPrice, 2);
            }
            else
            {
                if (is_member)//已认证会员
                {
                    // 1、规格--固定价格--会员
                    discount = Utils.ObjToDecimal(DbHelperSQL.GetSingle(sql + "is_goods = 0 and goods_product_category_id = " + goodsId + " and is_user = 0 and user_or_group_id = " + companyId + " and price_type = 0"), 0);
                    if (discount > 0)
                    {
                        discount_remark = "1、规格--固定价格--会员：" + goods_title + "--￥" + discount + "--" + company_name;
                        return discount;
                    }

                    // 2、规格--固定价格--组别
                    discount = Utils.ObjToDecimal(DbHelperSQL.GetSingle(sql + "is_goods = 0 and goods_product_category_id = " + goodsId + " and is_user = 1 and user_or_group_id = " + userGroupId + " and price_type = 0"), 0);
                    if (discount > 0)
                    {
                        discount_remark = "2、规格--固定价格--组别：" + goods_title + "--￥" + discount + "--" + group_name;
                        return discount;
                    }

                    // 3、规格--百分比--会员
                    discount = Utils.ObjToDecimal(DbHelperSQL.GetSingle(sql + "is_goods = 0 and goods_product_category_id = " + goodsId + " and is_user = 0 and user_or_group_id = " + companyId + " and price_type = 1"), 0);
                    if (discount > 0)
                    {
                        discount_remark = "3、规格--百分比--会员：" + goods_title + "--" + discount + "%--" + company_name;
                        return decimal.Round((discount / 100) * orgPrice, 2);
                    }

                    // 4、规格--百分比--组别
                    discount = Utils.ObjToDecimal(DbHelperSQL.GetSingle(sql + "is_goods = 0 and goods_product_category_id = " + goodsId + " and is_user = 1 and user_or_group_id = " + userGroupId + " and price_type = 1"), 0);
                    if (discount > 0)
                    {
                        discount_remark = "4、规格--百分比--组别：" + goods_title + "--" + discount + "%--" + group_name;
                        return decimal.Round((discount / 100) * orgPrice, 2);
                    }

                    // 5、商品--百分比--会员（商品无法设置固定价）
                    discount = Utils.ObjToDecimal(DbHelperSQL.GetSingle(sql + "is_goods = 1 and goods_product_category_id = " + productId + " and is_user = 0 and user_or_group_id = " + companyId + " and price_type = 1"), 0);
                    if (discount > 0)
                    {
                        discount_remark = "5、商品--百分比--会员（商品无法设置固定价）：" + product_title + "--" + discount + "%--" + company_name;
                        return decimal.Round((discount / 100) * orgPrice, 2);
                    }

                    // 6、商品--百分比--组别（商品无法设置固定价）
                    discount = Utils.ObjToDecimal(DbHelperSQL.GetSingle(sql + "is_goods = 1 and goods_product_category_id = " + productId + " and is_user = 1 and user_or_group_id = " + userGroupId + " and price_type = 1"), 0);
                    if (discount > 0)
                    {
                        discount_remark = "6、商品--百分比--组别（商品无法设置固定价）：" + product_title + "--" + discount + "%--" + group_name;
                        return decimal.Round((discount / 100) * orgPrice, 2);
                    }

                    // 7、品类--百分比--会员（品类无法设置固定价）
                    discount = Utils.ObjToDecimal(DbHelperSQL.GetSingle(sql + "is_goods = 2 and goods_product_category_id = " + categoryId + " and is_user = 0 and user_or_group_id = " + companyId + " and price_type = 1"), 0);
                    if (discount > 0)
                    {
                        discount_remark = "7、品类--百分比--会员（品类无法设置固定价）：" + category_title + "--" + discount + "%--" + company_name;
                        return decimal.Round((discount / 100) * orgPrice, 2);
                    }

                    // 8、品类--百分比--组别（品类无法设置固定价）
                    discount = Utils.ObjToDecimal(DbHelperSQL.GetSingle(sql + "is_goods = 2 and goods_product_category_id = " + categoryId + " and is_user = 1 and user_or_group_id = " + userGroupId + " and price_type = 1"), 0);
                    if (discount > 0)
                    {
                        discount_remark = "8、品类--百分比--组别（品类无法设置固定价）：" + category_title + "--" + discount + "%--" + group_name;
                        return decimal.Round((discount / 100) * orgPrice, 2);
                    }

                    discount_remark = "9、会员组别--百分比：" + group_name + "--" + groupDiscount + "%";
                    //如果不在差异价格表中，就按原价*会员所在组别的折扣价计算
                    return decimal.Round(groupDiscount * orgPrice, 2);
                }
                else//在会员没有认证时，如果显示价格，就按会员组别中定义的折扣来算：9、会员组别--百分比
                {
                    discount_remark = "9、会员组别--百分比：" + group_name + "--" + groupDiscount + "%";
                    return decimal.Round(groupDiscount * orgPrice, 2);
                }
            }
        }
        #endregion
    }
}

